package ranveterinaria.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import ranveterinaria.excepcion.DAOExcepcion;
import ranveterinaria.modelo.DuenoModelo;
import ranveterinaria.util.ConexionBD;

public class DuenoDAO extends BaseDAO {

	
	public void insertar(DuenoModelo vo) throws DAOExcepcion {
		String query = "insert into dueno(nombre_dueno,email_dueno,direccion_dueno,tipo_img_dueno,img_dueno,ruc_dueno,tlf_casa_dueno,tlf_cel_dueno) values (?,?,?,?,?,?,?,?)";
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setString(1, vo.getNombreDueno() );
			stmt.setString(2, vo.getEmailDueno() );
			stmt.setString(3, vo.getDireccionDueno() );
			stmt.setString(4, vo.getTipoImgDueno() );
			stmt.setString(5, vo.getImgDueno() );
			stmt.setString(6, vo.getRucDueno() );
			stmt.setString(7, vo.getTlfCasaDueno() );
			stmt.setString(8, vo.getTlfCelDueno() );	
			
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo insertar");
			}			

		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}		
	}

	
	 public Collection<DuenoModelo> listar() throws DAOExcepcion {
         Collection<DuenoModelo> c = new ArrayList<DuenoModelo>();
         Connection con = null;
         PreparedStatement stmt = null;
         ResultSet rs = null;
         try {
                 con = ConexionBD.obtenerConexion();
                 String query = "select codigo_dueno,nombre_dueno,email_dueno,direccion_dueno,tipo_img_dueno,img_dueno,ruc_dueno,tlf_casa_dueno,tlf_cel_dueno from dueno order by codigo_dueno desc";
                 stmt = con.prepareStatement(query);
                 rs = stmt.executeQuery();
                 while (rs.next()) {
                	 DuenoModelo vo = new DuenoModelo();
                         vo.setCodigoDueno(rs.getInt("codigo_dueno"));
                         vo.setNombreDueno(rs.getString("nombre_dueno"));
                         vo.setEmailDueno(rs.getString("email_dueno"));
                         vo.setDireccionDueno(rs.getString("direccion_dueno"));
                         vo.setTipoImgDueno(rs.getString("tipo_img_dueno"));
                         vo.setImgDueno (rs.getString("img_dueno"));
                         vo.setRucDueno(rs.getString("ruc_dueno"));
                         vo.setTlfCasaDueno(rs.getString("tlf_casa_dueno"));
                         vo.setTlfCelDueno(rs.getString("tlf_cel_dueno"));                         
                         c.add(vo);
                 }

         } catch (SQLException e) {
                 System.err.println(e.getMessage());
                 throw new DAOExcepcion(e.getMessage());
         } finally {
                 this.cerrarResultSet(rs);
                 this.cerrarStatement(stmt);
                 this.cerrarConexion(con);
         }
         return c;
 }
	
	 public DuenoModelo actualizar(DuenoModelo vo) throws DAOExcepcion {
          String query = "update dueno set nombre_dueno=? , email_dueno=? , direccion_dueno = ? , tipo_img_dueno=? , img_dueno=? , ruc_dueno = ? , tlf_casa_dueno =? , tlf_cel_dueno=?  where codigo_dueno=?";
          Connection con = null;
          PreparedStatement stmt = null;
          try {
                  con = ConexionBD.obtenerConexion();
                  stmt = con.prepareStatement(query);
                  stmt.setString(1, vo.getNombreDueno()  );
                  stmt.setString(2, vo.getEmailDueno() );
                  stmt.setString(3, vo.getDireccionDueno() );
                  stmt.setString(4, vo.getTipoImgDueno() );
                  stmt.setString(5, vo.getImgDueno() );
                  stmt.setString(6, vo.getRucDueno() );
                  stmt.setString(7, vo.getTlfCasaDueno() );
                  stmt.setString(8, vo.getTlfCelDueno() );
                  stmt.setInt(9, vo.getCodigoDueno() );
                  int i = stmt.executeUpdate();
                  if (i != 1) {
                          throw new SQLException("No se pudo actualizar");
                  }
          } catch (SQLException e) {
                  System.err.println(e.getMessage());
                  throw new DAOExcepcion(e.getMessage());
          } finally {
                  this.cerrarStatement(stmt);
                  this.cerrarConexion(con);
          }
          return vo;
  }
	 
	 public void eliminar(int idDueno) throws DAOExcepcion {
         String query = "delete from dueno WHERE codigo_dueno=?";
         Connection con = null;
         PreparedStatement stmt = null;
         try {
                 con = ConexionBD.obtenerConexion();
                 stmt = con.prepareStatement(query);
                 stmt.setInt(1, idDueno);
                 int i = stmt.executeUpdate();
                 if (i != 1) {
                         throw new SQLException("No se pudo eliminar");
                 }
         } catch (SQLException e) {
                 System.err.println(e.getMessage());
                 throw new DAOExcepcion(e.getMessage());
         } finally {
                 this.cerrarStatement(stmt);
                 this.cerrarConexion(con);
         }
 }
	 
	 public Collection<DuenoModelo> listar(DuenoModelo voo) throws DAOExcepcion {
         Collection<DuenoModelo> c = new ArrayList<DuenoModelo>();
         Connection con = null;
         PreparedStatement stmt = null;
         ResultSet rs = null;
         try {
                 con = ConexionBD.obtenerConexion();
                 String query = "select codigo_dueno,nombre_dueno,email_dueno,direccion_dueno,tipo_img_dueno,img_dueno,ruc_dueno,tlf_casa_dueno,tlf_cel_dueno from dueno "                		
                 		+ " where nombre_dueno like '%"+voo.getNombreDueno()+"%' and email_dueno like '%"+voo.getEmailDueno()+"%' and  ruc_dueno like '%"+voo.getRucDueno()+"%' and tlf_casa_dueno like '%"+voo.getTlfCasaDueno()+"%' and tlf_cel_dueno like '%"+voo.getTlfCelDueno()+"%' ";                 
                 
                 stmt = con.prepareStatement(query);
                 rs = stmt.executeQuery();
                 while (rs.next()) {
                	 DuenoModelo vo = new DuenoModelo();
                         vo.setCodigoDueno(rs.getInt("codigo_dueno"));
                         vo.setNombreDueno(rs.getString("nombre_dueno"));
                         vo.setEmailDueno(rs.getString("email_dueno"));
                         vo.setDireccionDueno(rs.getString("direccion_dueno"));
                         vo.setTipoImgDueno(rs.getString("tipo_img_dueno"));
                         vo.setImgDueno (rs.getString("img_dueno"));
                         vo.setRucDueno(rs.getString("ruc_dueno"));
                         vo.setTlfCasaDueno(rs.getString("tlf_casa_dueno"));
                         vo.setTlfCelDueno(rs.getString("tlf_cel_dueno"));                        
                         c.add(vo);
                 }

         } catch (SQLException e) {
                 System.err.println(e.getMessage());
                 throw new DAOExcepcion(e.getMessage());
         } finally {
                 this.cerrarResultSet(rs);
                 this.cerrarStatement(stmt);
                 this.cerrarConexion(con);
         }
         return c;
 }
	
	 public DuenoModelo obtener(String mail) throws DAOExcepcion {
		 DuenoModelo c = null;
	        Connection con = null;
	        PreparedStatement stmt = null;
	        ResultSet rs = null;
	        
	        try {
	                con = ConexionBD.obtenerConexion();
	                String query = "select codigo_dueno,nombre_dueno,email_dueno,direccion_dueno,tipo_img_dueno,img_dueno,ruc_dueno,tlf_casa_dueno,tlf_cel_dueno from dueno where email_dueno = ?";
	                stmt = con.prepareStatement(query);
	                stmt.setString(1, mail);
	                rs = stmt.executeQuery();
	                while (rs.next()) {
	                	DuenoModelo vo = new DuenoModelo();
	                	vo.setCodigoDueno(rs.getInt("codigo_dueno"));
                        vo.setNombreDueno(rs.getString("nombre_dueno"));
                        vo.setEmailDueno(rs.getString("email_dueno"));
                        vo.setDireccionDueno(rs.getString("direccion_dueno"));
                        vo.setTipoImgDueno(rs.getString("tipo_img_dueno"));
                        vo.setImgDueno (rs.getString("img_dueno"));
                        vo.setRucDueno(rs.getString("ruc_dueno"));
                        vo.setTlfCasaDueno(rs.getString("tlf_casa_dueno"));
                        vo.setTlfCelDueno(rs.getString("tlf_cel_dueno"));                                            
	                    return vo;
	                }

	        } catch (SQLException e) {
	                System.err.println(e.getMessage());
	                throw new DAOExcepcion(e.getMessage());
	        } finally {
	                this.cerrarResultSet(rs);
	                this.cerrarStatement(stmt);
	                this.cerrarConexion(con);
	        }
	        return c;
		}
	 
	 public Collection<DuenoModelo> buscarPorNombre(String nombreDueno) throws DAOExcepcion {
         Collection<DuenoModelo> c = new ArrayList<DuenoModelo>();
         Connection con = null;
         PreparedStatement stmt = null;
         ResultSet rs = null;
         try {
                 con = ConexionBD.obtenerConexion();
                 String query = "select codigo_dueno,nombre_dueno,email_dueno,direccion_dueno,tipo_img_dueno,img_dueno,ruc_dueno,tlf_casa_dueno,tlf_cel_dueno from dueno where nombre_dueno like (?) order by codigo_dueno desc";
                 stmt = con.prepareStatement(query);
                 stmt.setString(1,"%"+nombreDueno+"%");
                 rs = stmt.executeQuery();
                 while (rs.next()) {
                	 DuenoModelo vo = new DuenoModelo();
                         vo.setCodigoDueno(rs.getInt("codigo_dueno"));
                         vo.setNombreDueno(rs.getString("nombre_dueno"));
                         vo.setEmailDueno(rs.getString("email_dueno"));
                         vo.setDireccionDueno(rs.getString("direccion_dueno"));
                         vo.setTipoImgDueno(rs.getString("tipo_img_dueno"));
                         vo.setImgDueno (rs.getString("img_dueno"));
                         vo.setRucDueno(rs.getString("ruc_dueno"));
                         vo.setTlfCasaDueno(rs.getString("tlf_casa_dueno"));
                         vo.setTlfCelDueno(rs.getString("tlf_cel_dueno"));                         
                         c.add(vo);
                 }

         } catch (SQLException e) {
                 System.err.println(e.getMessage());
                 throw new DAOExcepcion(e.getMessage());
         } finally {
                 this.cerrarResultSet(rs);
                 this.cerrarStatement(stmt);
                 this.cerrarConexion(con);
         }
         return c;
	 }
}
